﻿if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UserSelectByUserName]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_UserSelectByUserName]
go

create procedure [dbo].[sp_UserSelectByUserName]
(
	@UserName varchar(20)
)

as

set nocount on

select [Id],
	[UserName],
	[Password],
	[Type],
	[Email],
	[Active],
	[Address],
	[Name],
	[Birthday],
	[Phone],
	[Picture]
	
from [User]
where [UserName] = @userName
/**************************************************************************
*                         TRIGGER
*******************************************************************/

GO
if exists (select * from sysobjects WHERE name = 'fn_NextID' AND type = 'fn')
    drop function fn_NextID
GO
-- @lastid là mã cuối cùng (fixwidth)
-- @prefix là tiền tố mã: vd HS0001 => HS
-- @size là số lượng ký tự trong mã HS0001 => 6
CREATE function fn_NextID (@lastid varchar(10),@prefix varchar(10),@size int)
  returns varchar(10)
as
    BEGIN
        IF(@lastid = '')
            set @lastid = @prefix + REPLICATE (0,@size - LEN(@prefix)) 
        declare @num_nextid int, @nextid varchar(10)
        set @lastid = LTRIM(RTRIM(@lastid))
        -- number next id
        set @num_nextid = replace(@lastid,@prefix,'') + 1
        -- bo di so luong ky tu tien to
        set @size = @size - len(@prefix)
        -- replicate số lượng số 0 REPLICATE(0,3) => 000
        set @nextid = @prefix + REPLICATE (0,@size - LEN(@prefix)) 
        set @nextid = @prefix + RIGHT(REPLICATE(0, @size) + CONVERT (VARCHAR(MAX), @num_nextid), @size)
        return @nextid
    END;
	
	
/******************************************************************/

GO
if exists (select * from sysobjects WHERE name = 'tr_NextID_Unit' AND type = 'TR')
    drop trigger tr_NextID_Unit
GO
create trigger tr_NextID_Unit on [Unit]
for insert
as
    begin
        DECLARE @lastid nvarchar(10)
        SET @lastid  = (SELECT TOP 1 id from Unit order by id desc)
        UPDATE Unit set id = dbo.fn_NextID (@lastid,'DVT',10) where id = ''
    end
	
	
/********************************************************************/	
	
GO
if exists (select * from sysobjects WHERE name = 'tr_NextID_Customer' AND type = 'TR')
    drop trigger tr_NextID_Customer
GO
create trigger tr_NextID_Customer on Customer
for insert
as
    begin
        DECLARE @lastid nvarchar(10)
        SET @lastid  = (SELECT TOP 1 id from Customer order by id desc)
        UPDATE Customer set id = dbo.fn_NextID (@lastid,'KHG',10) where id = ''
    end
	
	
/********************************************************************/	
GO
if exists (select * from sysobjects WHERE name = 'tr_NextID_OrderForm' AND type = 'TR')
    drop trigger tr_NextID_OrderForm
GO
create trigger tr_NextID_OrderForm on OrderForm
for insert
as
    begin
        DECLARE @lastid nvarchar(10)
        SET @lastid  = (SELECT TOP 1 id from OrderForm order by id desc)
        UPDATE OrderForm set id = dbo.fn_NextID (@lastid,'DHG',10) where id = ''
    end
	
	
/********************************************************************/	
GO
if exists (select * from sysobjects WHERE name = 'tr_NextID_Material' AND type = 'TR')
    drop trigger tr_NextID_Material
GO
create trigger tr_NextID_Material on Material
for insert
as
    begin
        DECLARE @lastid nvarchar(10)
        SET @lastid  = (SELECT TOP 1 id from Material order by id desc)
        UPDATE Material set id = dbo.fn_NextID (@lastid,'VTU',10) where id = ''
    end
	
	
/********************************************************************/	
GO
if exists (select * from sysobjects WHERE name = 'tr_NextID_User' AND type = 'TR')
    drop trigger tr_NextID_User
GO
create trigger tr_NextID_User on [User]
for insert
as
    begin
        DECLARE @lastid nvarchar(10)
        SET @lastid  = (SELECT TOP 1 id from  [User] order by id desc)
        UPDATE [User] set id = dbo.fn_NextID (@lastid,'NVN',10) where id = ''
    end
	
	
/********************************************************************/	
GO
if exists (select * from sysobjects WHERE name = 'tr_NextID_Repository' AND type = 'TR')
    drop trigger tr_NextID_Repository
GO
create trigger tr_NextID_Repository on Repository
for insert
as
    begin
        DECLARE @lastid nvarchar(10)
        SET @lastid  = (SELECT TOP 1 id from Repository order by id desc)
        UPDATE Repository set id = dbo.fn_NextID (@lastid,'KHO',10) where id = ''
    end
	
	
/********************************************************************/		
	
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MaterialSelectByIdUnit]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MaterialSelectByIdUnit]
go
create procedure [dbo].[sp_MaterialSelectByIdUnit]
(
	@idunit nchar(10)
)

as

set nocount on

select m.[Id],
	[Name],
	[IdUnit],
	[Description],
	[PriceByDay], 
[UnitName]
from [Material] m, Unit u
where m.IdUnit =@idunit and u.id = @idunit	

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ViewMaterialSelectAll]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_ViewMaterialSelectAll]
go

create procedure [dbo].[sp_ViewMaterialSelectAll]

as

set nocount on
SELECT [MaterialId]
      ,[MaterialName]
      ,[UnitId]
      ,[UnitName]
      ,[PriceByDay]
      ,[Amount]
      ,[Remaining]
      ,[RepositoryName]
      ,[Address]
      ,[RepositoryId]
  FROM [QLVT].[dbo].[ViewMaterial]
	
go



/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ViewMaterialSelectByRepository]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_ViewMaterialSelectByRepository]
go

create procedure [dbo].[sp_ViewMaterialSelectByRepository]
(
@RepositoryId nchar(10)
)
as

set nocount on
SELECT [MaterialId]
      ,[MaterialName]
      ,[UnitId]
      ,[UnitName]
      ,[PriceByDay]
      ,[Amount]
      ,[Remaining]
      ,[RepositoryName]
      ,[Address]
      ,[RepositoryId]
  FROM [QLVT].[dbo].[ViewMaterial]
	where [RepositoryId] = @RepositoryId
go

/******************************************************************************
******************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ViewMaterialSelectByRepositoryAndMaterial]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_ViewMaterialSelectByRepositoryAndMaterial]
go

create procedure [dbo].[sp_ViewMaterialSelectByRepositoryAndMaterial]
(
@RepositoryId nchar(10), @MaterialId nchar(10)
)
as

set nocount on
SELECT [MaterialId]
      ,[MaterialName]
      ,[UnitId]
      ,[UnitName]
      ,[PriceByDay]
      ,[Amount]
      ,[Remaining]
      ,[RepositoryName]
      ,[Address]
      ,[RepositoryId]
  FROM [QLVT].[dbo].[ViewMaterial]
	where [RepositoryId] = @RepositoryId and [MaterialId] = @MaterialId
go


/***********************************************************************
***************************************************************************/


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MaterialSelectByRepository]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_MaterialSelectByRepository]
go

create procedure [dbo].[sp_MaterialSelectByRepository]
(
	@RepositoryId nchar(10)
)
as

set nocount on

select m.[Id],
	[Name],
	[IdUnit],
	u.[UnitName],
	[Description],
	[PriceByDay], 
	[UnitName],
	rm.[Remaining]
from Material m, Unit u, Repository_Material rm
where
m.IdUnit = u.Id and m.[Id] = rm.[IdMaterial] and rm.[IdRepository] = @RepositoryId


/************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderFormInsert2]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderFormInsert2]
go

create procedure [dbo].[sp_OrderFormInsert2]
(
	@Id nchar(10),
	@IdCustomer nchar(10),
	@Vat int,
	@Discount int,
	@Building nvarchar(50),
	@OrderId nchar(10) output
)

as

set nocount on

insert into [OrderForm]
(
	[Id],
	[IdCustomer],
	[Vat],
	[Discount],
	[Building]
)
values
(
	@Id,
	@IdCustomer,
	@Vat,
	@Discount,
	@Building
)
set @OrderId = Cast ((select TOP 1 [Id] from [OrderForm] order by [Id] desc) as nchar)
/************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_OrderFormDelete]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_OrderFormDelete]
go

create procedure [dbo].[sp_OrderFormDelete]
(
	@Id nchar(10)
)

as

set nocount on
exec sp_OrderForm_MaterialDeleteAllByIdOrderForm @Id
delete from [OrderForm]
where [Id] = @Id

/*************************************************************************************/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ViewOrderSelectAll]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_ViewOrderSelectAll]
go

create procedure [dbo].[sp_ViewOrderSelectAll]

as

SELECT [CustomerId]
      ,[CustomerName]
      ,[Address]
      ,[OrderFormId]
      ,[Building]
      ,[Vat]
      ,[Discount]
      ,[MaterialName]
      ,[UnitId]
      ,[UnitName]
      ,[Amount]
      ,[PriceByDay]
      ,[RepositoryId]
      ,[RentDay]
      ,[ReturnDay]
      ,[TypeId]
      ,[MaterialId]
  FROM [QLVT].[dbo].[ViewOrder]
  
/*************************************************************************************/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ViewOrderSelectByCustomerAndType]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_ViewOrderSelectByCustomerAndType]
go

create procedure [dbo].[sp_ViewOrderSelectByCustomerAndType]
(
		@CustomerId nchar(10),
		@TypeId int
)
as

SELECT [CustomerId]
      ,[CustomerName]
      ,[Address]
      ,[OrderFormId]
      ,[Building]
      ,[Vat]
      ,[Discount]
      ,[MaterialName]
      ,[UnitId]
      ,[UnitName]
      ,[Amount]
      ,[PriceByDay]
      ,[RepositoryId]
      ,[RentDay]
      ,[ReturnDay]
      ,[TypeId]
      ,[MaterialId]
  FROM [QLVT].[dbo].[ViewOrder]
  
WHERE [CustomerId] = @CustomerId AND [TypeId] = @TypeId

/*******************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Repository_MaterialAdjustRemainingAmount]') and ObjectProperty(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_Repository_MaterialAdjustRemainingAmount]
go

create procedure [dbo].[sp_Repository_MaterialAdjustRemainingAmount]
(
		@RepositoryId nchar(10),
		@MaterialId nchar(10),
		@Amount int
)
as

set nocount on

update [Repository_Material]
set 
	[Remaining] = [Remaining] + @Amount
where [IdRepository] = @RepositoryId and [IdMaterial] = @MaterialId
  
go

